/*
 * BSM6.0
 *
 * Copyright 2012-2013 (C) SINODATA CO., LTD. All Rights Reserved.
 */
package com.sinodata.bsm.inmp.opconf;

/**
 * <p>
 * Description: 
 * </p>
 *
 * @author wangyazhou
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2013-5-15 下午3:24:28          wangyazhou        1.0         To create
 * </p>
 *
 * @since 
 * @see     
 */
public class OpSqlGenerator {

    /**
     * 获取所有网络设备的CPU利用率
     * @param table
     * @return
     */
    public static String buildCpuUtilizationSql(String table, String sTime, String eTime) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append(" pd.agent,");
        //        sb.append(" sda.pollid,");
        //        sb.append(" sda.INSTANCE,");
        sb.append(" sda.ttime,");
        sb.append(" sda.val ");
        sb.append(" FROM");
        sb.append(" polleddata pd,");
        sb.append(" " + table + " sda");
        sb.append(" WHERE");
        sb.append(" pd. ID = sda.pollid");
        sb.append(" AND (");
        sb.append(" pd. NAME LIKE '%CPUUtilization%'");
        sb.append(" OR pd. NAME LIKE '%JuniperCPUload%'");
        sb.append(" OR pd. NAME LIKE '%NortelCPU%'");
        sb.append(" )");
        sb.append(" AND sda.ttime >= '" + sTime + "'");
        sb.append(" AND sda.ttime < '" + eTime + "'");
        sb.append(" AND sda.instance ='-1'");
        sb.append(" ORDER BY");
        sb.append(" sda.ttime asc");
        return sb.toString();
    }

    /**
     * 获取最近的事件列表
     * 不要改变列的顺序
     * @param sTime
     * @param eTime
     * @return
     */
    public static String buildEventSql(String sTime, String eTime) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select");
        sb.append(" a.createtime,");
        sb.append(" a.modtime,");
        sb.append(" a.previousseverity,");
        sb.append(" a.severity,");
        sb.append(" a.mmessage,");
        sb.append(" a.source,");
        sb.append(" a.entity,");
        sb.append(" a.eventtype,");
        sb.append(" a.alarmcode,");
        sb.append(" a.id");
        sb.append(" FROM");
        sb.append(" alert a");
        sb.append(" where ");
        sb.append(" a.createtime >='" + sTime + "'");
        sb.append(" and a.createtime<'" + eTime + "'");
        sb.append(" OR(");
        sb.append(" a.modtime >='" + sTime + "'");
        sb.append("  and a.modtime <'" + eTime + "'");
        sb.append(" )");
        sb.append(" order by a.createtime asc,");
        sb.append(" a.modtime asc");

        return sb.toString();
    }

    /**
     * 获取当前用来保存端口原始性能数据的表sql
     * @return
     */
    public static String buildIfRawTableSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" concat (");
        sb.append(" metatable.LOOKUPTABLENAME,");
        sb.append(" '_',");
        sb.append(" metatable.ARCHIVETABLENAME");
        sb.append(" ) AS tablename");
        sb.append(" FROM");
        sb.append(" MetaTable");
        sb.append(" WHERE");
        sb.append(" metatable.LOOKUPTABLENAME = 'IfHCRaw'");
        sb.append(" AND metatable.ENDTIME = '-1'");
        return sb.toString();
    }

    /**
     * 查询端口的历史性能指标
     * @param table
     * @param startTime
     * @param endTime
     * @return
     */
    public static String buildIfRawPropSql(String table, String startTime, String endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append(" ifhr.ID,");
        sb.append(" ifhr.collectiontime,");
        sb.append(" ifhr.inoctets/1000,");
        sb.append(" ifhr.outoctets/1000,");
        sb.append(" ifhr.indiscards/1000,");
        sb.append(" ifhr.outdiscards/1000,");
        sb.append(" ifhr.inerrors/1000,");
        sb.append(" ifhr.outerrors/1000,");
        sb.append(" case sif.ifspeedin when 0 then 0 else round(ifhr.inoctets * 8 * 1000*100.0 / ifhr.deltatime/sif.ifspeedin,2) end as inusage ,");
        sb.append(" case sif.ifspeedout when 0 then 0 else round(ifhr.outoctets * 8 * 1000*100.0 / ifhr.deltatime/sif.ifspeedout,2) end as outusage");
        sb.append(" FROM " + table + " ifhr ,");
        sb.append(" managedobject mob,");
        sb.append(" snmpinterface sif");
        sb.append(" WHERE");
        sb.append(" ifhr.collectiontime >= '" + startTime + "'");
        sb.append(" and");
        sb.append(" ifhr.collectiontime < '" + endTime + "'");
        sb.append(" and mob.moid = ifhr.id");
        sb.append(" and mob.name = sif.name");
        sb.append(" order by ifhr.collectiontime asc");

        return sb.toString();
    }

    /**
     * 获取所有网络设备的响应时间
     * @param table
     * @return
     */
    public static String buildResponseSql(String table, String sTime, String eTime) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append(" pd.agent,");
        //        sb.append(" sda.pollid,");
        //        sb.append(" sda.INSTANCE,");
        sb.append(" sda.ttime,");
        sb.append(" sda.val");
        sb.append(" FROM");
        sb.append(" polleddata pd,");
        sb.append(" " + table + " sda");
        sb.append(" WHERE");
        sb.append(" pd. ID = sda.pollid");
        sb.append(" AND pd. NAME LIKE '%stat%'");
        sb.append(" AND sda. INSTANCE LIKE '%Device%'");
        sb.append(" AND sda.ttime >= '" + sTime + "'");
        sb.append(" AND sda.ttime < '" + eTime + "'");
        sb.append(" ORDER BY");
        sb.append(" sda.ttime asc");
        return sb.toString();
    }

    /**
     * 获取所有网络设备的内存利用率
     * @param table
     * @return
     */
    public static String buildMemUtilizationSql(String table, String sTime, String eTime) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT");
        sb.append(" pd.agent,");
        //        sb.append(" sda.pollid,");
        //        sb.append(" sda.INSTANCE,");
        sb.append(" sda.ttime,");
        sb.append(" sda.val");
        sb.append(" FROM");
        sb.append(" polleddata pd,");
        sb.append(" " + table + " sda");
        sb.append(" WHERE");
        sb.append(" pd. ID = sda.pollid");
        sb.append(" AND (");
        sb.append(" pd.NAME LIKE '%MemoryUtilization%'");
        sb.append(" OR pd.NAME LIKE '%NortelMem%'");
        sb.append(" )");
        sb.append(" AND sda.ttime >= '" + sTime + "'");
        sb.append(" AND sda.ttime < '" + eTime + "'");
        sb.append(" ORDER BY");
        sb.append("  sda.ttime asc");

        return sb.toString();
    }

    /**
     * 获取当前保存端口性能数据 正在使用的表名
     * @return
     */
    public static String getCurrIfTable() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" concat (metatable.LOOKUPTABLENAME,");
        sb.append(" '_',");
        sb.append(" metatable.ARCHIVETABLENAME");
        sb.append(" ) AS tablename");
        sb.append(" FROM");
        sb.append(" MetaTable");
        sb.append(" WHERE");
        sb.append(" WHERE");
        sb.append(" metatable.LOOKUPTABLENAME = 'IfHCRaw'");
        sb.append(" AND metatable.ENDTIME = '-1'");

        return sb.toString();
    }

    /**
     * 获取所有的端口详细列表
     * @return res+attr
     */
    public static String buildIfDetailSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" sif. NAME,");
        sb.append(" sif.ifname,");
        sb.append(" sif.ifdescr,");
        sb.append(" '' as ip,");
        sb.append(" '' as restype,");
        sb.append(" sif.ifindex,");
        sb.append(" sif.hostnetmask,");
        sb.append(" sif.ifspeedin,");
        sb.append(" sif.ifspeedout,");
        sb.append(" sif.physaddr,");
        sb.append(" tobj.ipaddress as connip,");
        sb.append(" sif.sysoid,");
        sb.append(" sif.ifadminstatus,");
        sb.append(" sif.ifoperstatus");
        sb.append(" FROM");
        sb.append(" snmpinterface sif,");
        sb.append(" topoobject   tobj");
        sb.append(" where sif.name = tobj.name;");

        return sb.toString();
    }

    /**
     * 获取所有的网络详细信息列表
     * 
     * @return  res+attr
     */
    public static String buildNwDetailSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" omo. NAME as inid,");
        sb.append(" mobj.displayname,");
        sb.append(" sn.sysdescr as desc_,");
        sb.append(" tpobj.ipaddress,");
        sb.append(" omo.category,");
        sb.append(" sn.sysoid,");
        sb.append(" vd.name as vendor,");
        sb.append(" sn.sysdescr as devdesc");
        sb.append(" FROM");
        sb.append(" opmanagerobject omo,");
        sb.append("  managedobject mobj,");
        sb.append(" snmpnode sn,");
        sb.append(" topoobject tpobj,");
        sb.append("  VENDORDETAILS  vd");
        sb.append(" WHERE");
        sb.append("  omo. NAME = mobj. NAME");
        sb.append(" AND omo. NAME = sn. NAME");
        sb.append(" AND omo. NAME = tpobj. NAME");
        sb.append(" and vd.vendorid= omo.vendorid");
        sb.append(" AND (");
        sb.append(" omo.category LIKE '%Switch%'");
        sb.append(" OR omo.category LIKE '%Firewall%'");
        sb.append(" OR omo.category LIKE '%Router%'");
        sb.append(" OR omo.category LIKE '%安全设备%'");
        sb.append(" OR omo.category LIKE '%LoadBalancer%'");
        sb.append(")");

        return sb.toString();
    }

    /**
     * 获取所有的网络设备列表
     * @return  attr
     */
    public static String buildNwListSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" omo. NAME,");
        sb.append(" mobj.displayname,");
        sb.append(" sn.sysdescr,");
        sb.append(" tpobj.ipaddress,");
        sb.append(" omo.category");
        sb.append(" FROM");
        sb.append(" opmanagerobject omo,");
        sb.append(" managedobject mobj,");
        sb.append(" snmpnode sn,");
        sb.append(" topoobject tpobj");
        sb.append(" WHERE");
        sb.append(" omo. NAME = mobj. NAME");
        sb.append(" AND omo. NAME = sn. NAME");
        sb.append(" AND omo. NAME = tpobj. NAME");
        sb.append(" AND (");
        sb.append(" omo.category LIKE '%Switch%'");
        sb.append(" OR omo.category LIKE '%Firewall%'");
        sb.append("  OR omo.category LIKE '%Router%'");
        sb.append(" OR omo.category LIKE '%安全设备%'");
        sb.append(" OR omo.category LIKE '%LoadBalancer%'");
        sb.append(")");
        return sb.toString();
    }

    /**
     * 获得指定网络设备的端口列表
     * @param kpiName
     * @return
     */
    public static String bulidIfByNWSql(String kpiName) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT");
        sb.append(" concat(si. NAME,'_',mo.moid) as name,");
        sb.append(" mo.DISPLAYNAME,");
        sb.append(" si.IFDESCR,");
        sb.append(" '' AS resType,");
        sb.append(" si.IFINDEX,");
        sb.append(" si.hostnetmask,");
        sb.append(" si.ifspeedin,");
        sb.append(" si.ifspeedout,");
        sb.append(" si.physaddr,");
        sb.append(" tob.ipaddress AS connip,");
        sb.append(" si.sysoid,");
        sb.append(" si.ifadminstatus,");
        sb.append(" si.ifoperstatus,");
        sb.append(" si.IFNAME");
        sb.append(" FROM");
        sb.append(" SnmpInterface si");
        sb.append(" INNER JOIN ManagedObject mo ON si. NAME = mo. NAME");
        sb.append(" INNER JOIN topoobject tob ON si. NAME = tob. NAME");
        sb.append(" INNER JOIN IpAddress ipa ON si. NAME = ipa. NAME");
        sb.append(" AND ipa.parentnode = '" + kpiName + "'");
        sb.append(" and si.physmedia != 53 ");
        sb.append(" LEFT JOIN InterfaceDefinitionTemplate iftempl ON si.PHYSMEDIA = iftempl.TYPEID");
        sb.append(" order by si.ifindex");

        return sb.toString();
    }

}
